"""
Case Type   : mysql protocol compatibility
Case Name   : 协议兼容下验证自定义存储过程
Create At   : 2024/10/23
Owner       : lvlintao666
Description :
    1、设置协议兼容相关配置
    2、建表
    3、创建存储过程1
    4、创建存储过程2
    5、show存储过程
    6、清理环境
Expect      :
    1、配置成功
    2、建表成功
    3、创建存储过程1成功
    4、创建存储过程2成功
    5、show存储过程成功
    6、清理成功
History     :
"""

import os
import time
import unittest

from yat.test import Node
from yat.test import macro

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.CommonMySQL import CommonMySQL
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

logger = Logger()


class ProtoTestCase(unittest.TestCase):
    def setUp(self):
        logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.constant = Constant()
        self.com = Common()
        self.comsh = CommonSH('PrimaryDbUser')
        self.pri_node = Node('PrimaryDbUser')
        self.my_node = Node('mysql5')
        self.db_name = "proto_test_db"
        self.conf_path = os.path.join(macro.DB_INSTANCE_PATH,
                                      'postgresql.conf')
        self.create_db = f"drop database if exists {self.db_name};" \
                         f"create database {self.db_name} dbcompatibility='B'"
        self.user = f"proto_test_31"
        self.password = f"proto_test123"
        self.create_u = f"drop user if exists {self.user} cascade;" \
                        f"create user {self.user} with password '{self.password}';" \
                        f"select set_native_password('{self.user}','{self.password}','');"
        self.show_cmd = f"show enable_dolphin_proto;" \
                        f"show dolphin_server_port;"
        self.c_tb = f"drop table if exists tab_protocol_0031;" \
                    f"create table tab_protocol_0031 (col1 int ,col2 int);"
        self.procedure1 = f"delimiter // \n" \
                          f"create procedure pro_protocol_0031_01() \n" \
                          f"begin \n" \
                          f"insert into tab_protocol_0031 values(1,1),(2,2),(3,3); \n" \
                          f"end //\n" \
                          f"delimiter ; "
        self.procedure2 = f"delimiter // \n" \
                          f"create procedure pro_protocol_0031_02() \n" \
                          f"begin \n" \
                          f"update tab_protocol_0031 set col2 = 5 where col1 = 1; \n" \
                          f"end //\n" \
                          f"delimiter ; "
        self.call1 = f"call pro_protocol_0031_01();"
        self.call2 = f"call pro_protocol_0031_02();"
        self.query = f"show procedure status where Name like 'pro_protocol_0031_01'; " \
                     f"show procedure status where Name like 'pro_protocol_0031_02'; "
        self.clean_tb = f"drop procedure if exists pro_protocol_0031_01; " \
                        f"drop procedure if exists pro_protocol_0031_02; " \
                        f"drop table if exists tab_protocol_0031;" \
                        f"drop user if exists {self.user} cascade;"
        self.clean_db = f"drop database {self.db_name};"

    def test_index(self):
        text = '--step1:设置协议兼容相关配置;expect:创建成功--'
        logger.info(text)
        msg = self.comsh.execute_gsguc('set',
                                       self.constant.GSGUC_SUCCESS_MSG,
                                       'enable_dolphin_proto = on')
        logger.info(msg)
        my_port = int(self.pri_node.db_port) + 10
        msg = self.comsh.execute_gsguc('set',
                                       self.constant.GSGUC_SUCCESS_MSG,
                                       f'dolphin_server_port = {my_port}')
        logger.info(msg)
        sed_cmd = f"sed -i '$a dolphin.default_database_name={self.db_name}' " \
                  f"{self.conf_path}"
        logger.info(sed_cmd)
        res = self.com.get_sh_result(self.pri_node, sed_cmd)
        logger.info(res)

        msg = self.comsh.restart_db_cluster()
        logger.info(msg)
        show_res = self.comsh.execut_db_sql(self.show_cmd)
        logger.info(show_res)
        self.assertIn('on', show_res, '执行失败' + text)
        self.assertIn(f'{my_port}', show_res, '执行失败' + text)

        logger.info(self.create_db)
        db_res = self.comsh.execut_db_sql(self.create_db)
        logger.info(db_res)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, db_res,
                      '执行失败' + text)
        logger.info(self.create_u)
        u_res = self.comsh.execut_db_sql(self.create_u, dbname=self.db_name)
        logger.info(u_res)
        self.assertIn(self.constant.CREATE_ROLE_SUCCESS_MSG, u_res,
                      '执行失败' + text)

        text = '--step2:M*连接数据库建表;expect:创建成功--'
        logger.info(text)
        my_sh = CommonMySQL('mysql5')
        c_res = my_sh.execut_db_sql(sql=self.c_tb, dbname=self.db_name, host=self.pri_node.db_host,
                                    port=my_port, user=self.user, password=self.password)
        logger.info(c_res)

        text = '--step3:M*连接数据库创建并执行存储过程1;expect:创建成功--'
        logger.info(text)
        pro_res = my_sh.execut_db_sql(sql=self.procedure1, dbname=self.db_name, host=self.pri_node.db_host,
                                      port=my_port, user=self.user, password=self.password)
        logger.info(pro_res)
        self.assertNotIn('ERROR', pro_res, '执行失败' + text)
        exec_res = my_sh.execut_db_sql(sql=self.call1, dbname=self.db_name, host=self.pri_node.db_host,
                                      port=my_port, user=self.user, password=self.password)
        logger.info(exec_res)
        self.assertNotIn('ERROR', exec_res, '执行失败' + text)

        text = '--step4:M*连接数据库创建并执行存储过程2;expect:创建成功--'
        logger.info(text)
        pro_res = my_sh.execut_db_sql(sql=self.procedure2, dbname=self.db_name, host=self.pri_node.db_host,
                                      port=my_port, user=self.user, password=self.password)
        logger.info(pro_res)
        self.assertNotIn('ERROR', pro_res, '执行失败' + text)
        exec_res = my_sh.execut_db_sql(sql=self.call2, dbname=self.db_name, host=self.pri_node.db_host,
                                       port=my_port, user=self.user, password=self.password)
        logger.info(exec_res)
        self.assertNotIn('ERROR', exec_res, '执行失败' + text)

        text = '--step5:M*连接数据库查询存储过程;expect:创建成功--'
        logger.info(text)
        q_res = my_sh.execut_db_sql(sql=self.query, dbname=self.db_name, host=self.pri_node.db_host,
                                    port=my_port, user=self.user, password=self.password)
        logger.info(q_res)
        self.assertNotIn('ERROR', q_res, '执行失败' + text)

    def tearDown(self):
        text = '--step6:清理环境;expect:成功--'
        logger.info(text)
        clean_res = self.comsh.execut_db_sql(self.clean_tb, dbname=self.db_name)
        logger.info(clean_res)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, clean_res,
                      '执行失败' + text)
        self.assertIn(self.constant.DROP_ROLE_SUCCESS_MSG, clean_res,
                      '执行失败' + text)
        clean_res = self.comsh.execut_db_sql(self.clean_db)
        logger.info(clean_res)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, clean_res,
                      '执行失败' + text)
        msg = self.comsh.execute_gsguc('set',
                                       self.constant.GSGUC_SUCCESS_MSG,
                                       'enable_dolphin_proto = off')
        logger.info(msg)
        self.assertTrue(msg, '执行失败' + text)
        msg = self.comsh.execute_gsguc('set',
                                       self.constant.GSGUC_SUCCESS_MSG,
                                       f'dolphin_server_port = 3308')
        logger.info(msg)
        self.assertTrue(msg, '执行失败' + text)
        sed_cmd = f"sed -i '/dolphin.default_database_name={self.db_name}/d' " \
                  f"{self.conf_path}"
        logger.info(sed_cmd)
        res = self.com.get_sh_result(self.pri_node, sed_cmd)
        logger.info(res)
        msg = self.comsh.restart_db_cluster()
        logger.info(msg)
        status = self.comsh.get_db_cluster_status()
        logger.info(status)
        self.assertTrue("Degraded" in status or "Normal" in status)
        logger.info(f'-----{os.path.basename(__file__)} end-----')
